﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using Storage;
using Storage.EntityModels;
using System.Data.Linq;
using System.Transactions;
using Stats.Objects;

namespace Stats
{
    /// <summary>
    /// This class contains the necessary methods to calculate the statistics about the users and to manage the feedbacks system.
    /// </summary>
    public class AuxUserMethods
    {
        /// <summary>
        /// find on the DB the nickname of a user. Mainly used for the feedback methods
        /// </summary>
        /// <param name="userID">the Id of the user in the DB</param>
        /// <returns>the nick name as a string</returns>
        public static String getUserUsername(int userID)
        {
            String username = "";

            try
            {

                TripLoaDataContext db = Storage.StorageManager.Instance.Entities;

                var userEntry =
                    (from userEntries in db.Users
                     where userEntries.Id == userID
                     select userEntries).First();

                username = userEntry.Username;

            }
            catch (InvalidOperationException) { username = ""; }
            catch (NullReferenceException) { username = ""; }
            catch (System.Data.SqlClient.SqlException) { username = ""; }

            return username;

        }

        #region user-trip methods

        /// <summary>
        /// Calculates the number of trips a user partecipate as a simple passenger
        /// </summary>
        /// <param name="userID">the user ID</param>
        /// <returns>the number of trips</returns>
        public static int getTripsAsPassengerCount(int userID)
        {
            int tripsAsPassengerCount = 0;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                var userStagesEntries =
                    from userStageEntry in db.Users_Stages
                    where userStageEntry.UserId == userID
                    select userStageEntry;

                int[] tripID = new int[userStagesEntries.Count()];
                int i = 0;

                foreach (var userStageEntry in userStagesEntries)
                {
                    var tripIDs =
                        from stageEntry in db.Stages
                        where userStageEntry.StageId == stageEntry.Id
                        select stageEntry.TripId;

                    tripID[i] = (int)tripIDs.First();

                    i = i + 1;

                }

                List<int> tripIDsList = new List<int>(0);

                for (int k = 0; k < tripID.Length; k++)
                {
                    if (k == 0) tripIDsList.Add(tripID[0]);

                    if (!tripIDsList.Contains(tripID[k])) tripIDsList.Add(tripID[k]);
                }

                tripsAsPassengerCount = tripIDsList.Count;

            }
            catch (InvalidOperationException) { tripsAsPassengerCount = 0; }
            catch (NullReferenceException) { tripsAsPassengerCount = 0; }
            catch (System.Data.SqlClient.SqlException) { tripsAsPassengerCount = 0; }

            return tripsAsPassengerCount;


        }

        /// <summary>
        /// calculates the number of trips a user partecipate as car owner (driver)
        /// </summary>
        /// <param name="userID">the user ID</param>
        /// <returns>the number of trips</returns>
        public static int getTripsAsDriverCount(int userID)
        {
            int tripsAsDriverCount = 0;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                var tripEntries =
                    from tripEntry in db.Trips
                    where tripEntry.OwnerId == userID
                    select tripEntry;

                tripsAsDriverCount = tripEntries.Count();

                //var stageEntries =
                //    from stageEntry in db.Stages
                //    where stageEntry.DriverId == userID
                //    select stageEntry;

                //int[] tripIDs = new int[stageEntries.Count()];
                //int i = 0;

                //foreach (var stage in stageEntries)
                //{
                //    tripIDs[i] = stage.TripId;
                //    i = i + 1;
                //}

                //List<int> tripIDsList = new List<int>(0);

                //for (int k = 0; k < tripIDs.Length; k++)
                //{
                //    if (k == 0) tripIDsList.Add(tripIDs[0]);

                //    if (!tripIDsList.Contains(tripIDs.ElementAt(k))) tripIDsList.Add(tripIDs.ElementAt(k));
                //}

                //tripsAsDriverCount = tripIDsList.Count;

            }
            catch (InvalidOperationException) { tripsAsDriverCount = 0; }
            catch (NullReferenceException) { tripsAsDriverCount = 0; }
            catch (System.Data.SqlClient.SqlException) { tripsAsDriverCount = 0; }

            return tripsAsDriverCount;

        }

        /// <summary>
        /// returns the number of trips proposed by the user
        /// </summary>
        /// <param name="userID">The user ID</param>
        /// <returns>The number of trips proposed by the user</returns>
        public static int getUserTripsProposedNumber(int userID)
        {
            int userProposedTripsNumber = 0;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                userProposedTripsNumber =
                    (from tripEntry in db.Trips
                     where tripEntry.OwnerId == userID
                     select tripEntry).Count();
            }
            catch (InvalidOperationException) { userProposedTripsNumber = 0; }
            catch (NullReferenceException) { userProposedTripsNumber = 0; }
            catch (System.Data.SqlClient.SqlException) { userProposedTripsNumber = 0; }

            return userProposedTripsNumber;
        }

        #endregion

        #region user-statistics methods

        //UNUSED
        /// <summary>
        /// returns the id of all the passenger a driver carry in his TripLoa® history
        /// </summary>
        /// <param name="ownerID">the userId of the car owner</param>
        /// <returns>the list of all the passenger, saved as an arrayList</returns>
        public static ArrayList getTotalPassengersCarriedIDsList(int ownerID)
        {
            ArrayList IDs = new ArrayList();
            
            int? vehicleClass_used_inStage = 0;
            int? driverID = 0;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                var tripEntries =
                    from tripEntry in db.Trips
                    where tripEntry.OwnerId == ownerID
                    select tripEntry;


                foreach (var tripEntry in tripEntries)
                {
                    var stageEntriesForTrip =
                        from stageEntry in db.Stages
                        where stageEntry.TripId == tripEntry.Id
                        select stageEntry;

                    foreach (var stageEntry in stageEntriesForTrip)
                    {
                        vehicleClass_used_inStage = (int?) stageEntry.Users_VehicleDetails.VehicleDetails.VehicleClass;

                        /* checking if the vehicle used in the stage is a car o a motorbike */

                        if (vehicleClass_used_inStage == 0 || vehicleClass_used_inStage == 1)
                        {
                            driverID = stageEntry.DriverId;
                            
                            if (driverID != null && !IDs.Contains(driverID)) IDs.Add(driverID);

                            var passengerEntries =
                                from passengerEntry in db.Users_Stages
                                where passengerEntry.StageId == stageEntry.Id
                                select passengerEntry.UserId;

                            foreach (var passengerEntry in passengerEntries)
                            {
                                if (!IDs.Contains((int)passengerEntry)) IDs.Add((int)passengerEntry);
                            }

                        }

                    }

                }

                if (IDs.Contains((int)ownerID)) IDs.Remove((int)ownerID);

            }
            catch (NullReferenceException) { IDs = new ArrayList(); }
            catch (InvalidOperationException) { IDs = new ArrayList(); }
            catch (System.Data.SqlClient.SqlException) { IDs = new ArrayList(); }

            return IDs;

        }

        //UNUSED
        /// <summary>
        /// TODO
        /// </summary>
        /// <param name="ownerID"></param>
        /// <returns></returns>
        public static ArrayList getPassengersCarriedOnTheWayIDsList(int ownerID)
        {
            ArrayList usersCarriedOnTheWayIDsList = new ArrayList();

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                var tripEntries =
                    from tripEntry in db.Trips
                    where tripEntry.OwnerId == ownerID
                    select tripEntry;

                ArrayList usersFromOriginIDsList = new ArrayList();

                foreach (var tripEntry in tripEntries)
                {
                    var firstStageEntry =
                        from stageEntry in db.Stages
                        where stageEntry.TripId == tripEntry.Id && stageEntry.Ordinal == 1
                        select stageEntry;

                    var stageEntriesForTrip =
                        from stageEntry in db.Stages
                        where stageEntry.TripId == tripEntry.Id
                        select stageEntry;

                    if (stageEntriesForTrip.Count() == 1) return usersCarriedOnTheWayIDsList;

                    else
                    {
                        usersFromOriginIDsList.Add(firstStageEntry.First().DriverId);

                        var firstSEntry = firstStageEntry.First();
                        var passengerEntriesFromOrigin =
                            from passengerEntry in db.Users_Stages
                            where passengerEntry.StageId == firstSEntry.Id
                            select passengerEntry.UserId;

                        foreach (var passengerEntry in passengerEntriesFromOrigin)
                        {
                            usersFromOriginIDsList.Add((int)passengerEntry);
                        }

                        foreach (var stageEntry in stageEntriesForTrip)
                        {
                            if ((!usersFromOriginIDsList.Contains(stageEntry.DriverId)) && (!usersCarriedOnTheWayIDsList.Contains(stageEntry.DriverId))) usersCarriedOnTheWayIDsList.Add(stageEntry.DriverId);

                            var passengerEntries =
                                from passengerEntry in db.Users_Stages
                                where passengerEntry.StageId == stageEntry.Id
                                select passengerEntry.UserId;

                            foreach (var passengerEntry in passengerEntries)
                            {
                                if ((!usersFromOriginIDsList.Contains((int)passengerEntry)) && (!usersCarriedOnTheWayIDsList.Contains((int)passengerEntry))) usersCarriedOnTheWayIDsList.Add((int)passengerEntry);
                            }

                        }

                    }

                    usersFromOriginIDsList = new ArrayList();

                }

                if (usersCarriedOnTheWayIDsList.Contains((int)ownerID)) usersCarriedOnTheWayIDsList.Remove((int)ownerID);

            }
            catch (InvalidOperationException) { usersCarriedOnTheWayIDsList = new ArrayList(); }
            catch (NullReferenceException) { usersCarriedOnTheWayIDsList = new ArrayList(); }
            catch (System.Data.SqlClient.SqlException) { usersCarriedOnTheWayIDsList = new ArrayList(); }

            return usersCarriedOnTheWayIDsList;

        }

        /// <summary>
        /// returns all the passenger carried by the user in all trips done by himself
        /// </summary>
        /// <param name="userID">the user id</param>
        /// <returns>the number of passengers</returns>
        public static int getUserTotalCarriedPassengersCount(int userID)
        {
            int userTotalPSNCarried = 0;
            int additionalRequestedSeats = 0;

            int? driverID = 0;
            int? vehicleClass_used_inStage = 0;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                var tripEntries =
                    from tripEntry in db.Trips
                    where tripEntry.OwnerId == userID
                    select tripEntry;

                ArrayList IDs = new ArrayList();

                foreach (var tripEntry in tripEntries)
                {
                    var stageEntriesForTrip =
                        from stageEntry in db.Stages
                        where stageEntry.TripId == tripEntry.Id
                        select stageEntry;

                    foreach (var stageEntry in stageEntriesForTrip)
                    {
                        vehicleClass_used_inStage = (int?)stageEntry.Users_VehicleDetails.VehicleDetails.VehicleClass;

                        /* checking if the vehicle used in the stage is a car o a motorbike */

                        if (vehicleClass_used_inStage == 0 || vehicleClass_used_inStage == 1)
                        {
                            driverID = stageEntry.DriverId;

                            if (driverID != null && !IDs.Contains(driverID)) IDs.Add(driverID);

                            var passengerEntries =
                                from passengerEntry in db.Users_Stages
                                where passengerEntry.StageId == stageEntry.Id
                                select new { PsnID = passengerEntry.UserId, RequestedSeats = passengerEntry.RequestedSeats };

                            foreach (var passengerEntry in passengerEntries)
                            {
                                if (!IDs.Contains((int)passengerEntry.PsnID))
                                {
                                    IDs.Add((int)passengerEntry.PsnID);
                                    additionalRequestedSeats += (passengerEntry.RequestedSeats - 1);
                                }

                            }

                        }

                    }

                }

                if (IDs.Contains((int)userID)) { IDs.Remove((int)userID); }

                userTotalPSNCarried = IDs.Count + additionalRequestedSeats;

            }
            catch (InvalidOperationException) { userTotalPSNCarried = 0; }
            catch (NullReferenceException) { userTotalPSNCarried = 0; }
            catch (System.Data.SqlClient.SqlException) { userTotalPSNCarried = 0; }

            return userTotalPSNCarried;

        }

        /// <summary>
        /// returns the number of passengers carried in a stage different from the initial one
        /// </summary>
        /// <param name="ownerID">The Driver ID</param>
        /// <returns>the number of passengers carried on the way (not from the first stage)</returns>
        public static int getPassengersCarriedOnTheWayCount(int ownerID)
        {
            int userTotalPSNCarriedOnTheWay = 0;
            int additionalrequestedSeats = 0;

            int vehicleClass_used_inStage = 0;
            int? stageDriverID = 0;
            int? firstStageDriverID = 0;
            
            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                var tripEntries =
                    from tripEntry in db.Trips
                    where tripEntry.OwnerId == ownerID
                    select tripEntry;

                ArrayList usersFromOriginIDsList = new ArrayList();
                ArrayList usersCarriedOnTheWayIDsList = new ArrayList();

                bool skipTrip = false;

                foreach (var tripEntry in tripEntries)
                {
                    var firstStageEntry =
                        (from stageEntry in db.Stages
                        where stageEntry.TripId == tripEntry.Id && stageEntry.Ordinal == 1
                         select stageEntry).First(); ;

                    var stageEntriesForTrip =
                        from stageEntry in db.Stages
                        where stageEntry.TripId == tripEntry.Id
                        select stageEntry;

                    if (stageEntriesForTrip.Count() == 1 || stageEntriesForTrip.Count() == 0) skipTrip = true;

                    if (! skipTrip)
                    {
                        vehicleClass_used_inStage = (int) firstStageEntry.Users_VehicleDetails.VehicleDetails.VehicleClass;

                        /* checking if the vehicle used in the first stage is a car o a motorbike */

                        if (vehicleClass_used_inStage == 0 || vehicleClass_used_inStage == 1)
                        {

                            firstStageDriverID = firstStageEntry.DriverId;

                            if (firstStageDriverID != null) usersFromOriginIDsList.Add(firstStageDriverID);

                            var passengerEntriesFromOrigin =
                                from passengerEntry in db.Users_Stages
                                where passengerEntry.StageId == firstStageEntry.Id
                                select passengerEntry.UserId;


                            foreach (var passengerEntry in passengerEntriesFromOrigin)
                            {
                                usersFromOriginIDsList.Add((int)passengerEntry);
                            }

                        }

                        foreach (var stageEntry in stageEntriesForTrip)
                        {

                            vehicleClass_used_inStage = (int) stageEntry.Users_VehicleDetails.VehicleDetails.VehicleClass;
                            
                            /* checking if the vehicle used in the first stage is a car o a motorbike */

                            if (vehicleClass_used_inStage == 0 || vehicleClass_used_inStage == 1)
                            {

                                stageDriverID = stageEntry.DriverId;

                                if (stageDriverID != null && (!usersFromOriginIDsList.Contains(stageDriverID)) && (!usersCarriedOnTheWayIDsList.Contains(stageDriverID))) usersCarriedOnTheWayIDsList.Add(stageDriverID);

                                var passengerEntries =
                                    from passengerEntry in db.Users_Stages
                                    where passengerEntry.StageId == stageEntry.Id
                                    select new { PsnID = passengerEntry.UserId, RequestedSeats = passengerEntry.RequestedSeats };

                                foreach (var passengerEntry in passengerEntries)
                                {
                                    if ((!usersFromOriginIDsList.Contains((int)passengerEntry.PsnID)) && (!usersCarriedOnTheWayIDsList.Contains((int)passengerEntry.PsnID)))
                                    {
                                        usersCarriedOnTheWayIDsList.Add((int)passengerEntry.PsnID);
                                        additionalrequestedSeats += (passengerEntry.RequestedSeats - 1);
                                    }
                                }

                            }

                        }

                    }

                    usersFromOriginIDsList = new ArrayList();
                    skipTrip = false;

                }

                if (usersCarriedOnTheWayIDsList.Contains((int)ownerID)) { usersCarriedOnTheWayIDsList.Remove((int)ownerID); }

                userTotalPSNCarriedOnTheWay = usersCarriedOnTheWayIDsList.Count + additionalrequestedSeats;

            }
            catch (InvalidOperationException) { userTotalPSNCarriedOnTheWay = 0; }
            catch (NullReferenceException) { userTotalPSNCarriedOnTheWay = 0; }
            catch (System.Data.SqlClient.SqlException) { userTotalPSNCarriedOnTheWay = 0; }

            return userTotalPSNCarriedOnTheWay;

        }

        /// <summary>
        /// the max number of passenger carried in a trip by the owner
        /// </summary>
        /// <param name="ownerID">the userId of the car owner</param>
        /// <returns>the max number of passengers carried in a trip by the owner</returns>
        public static int getMaxPassengerCarriedInATrip(int ownerID)
        {
            int maxPSN = 0;
            int vehicleClass_used_inStage = 0;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                var tripEntries =
                    from tripEntry in db.Trips
                    where tripEntry.OwnerId == ownerID
                    select tripEntry;

                foreach (var tripEntry in tripEntries)
                {
                    var stageEntriesForTrip =
                        from stageEntry in db.Stages
                        where stageEntry.TripId == tripEntry.Id
                        select stageEntry;

                    foreach (var stageEntry in stageEntriesForTrip)
                    {
                        vehicleClass_used_inStage = (int) stageEntry.Users_VehicleDetails.VehicleDetails.VehicleClass;

                        /* checking if the vehicle used in the stage is a car o a motorbike */

                        if (vehicleClass_used_inStage == 0 || vehicleClass_used_inStage == 1)
                        {
                            if (maxPSN < stageEntry.BusySeats) maxPSN = stageEntry.BusySeats;
                        }
                    }
                }

            }
            catch (NullReferenceException) { maxPSN = 0; }
            catch (InvalidOperationException) { maxPSN = 0; }
            catch (System.Data.SqlClient.SqlException) { maxPSN = 0; }

            return maxPSN;

        }

        /// <summary>
        /// Calculates the emissions saved by a single user, both as driver or as passenger using TripLoa®
        /// </summary>
        /// <param name="userID">The Id of the user</param>
        /// <returns>the amount of CO2 saved in quintals</returns>
        public static double? getUserEmissionsSaved(int userID)
        {
            double? userEmissionsSaved = 0;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                var userStagesEntries =
                    from userStageEntry in db.Users_Stages
                    where userStageEntry.UserId == userID
                    select userStageEntry;

                foreach (var usrStage in userStagesEntries)
                {
                    userEmissionsSaved += AuxSavingsMethods.getEmissionsSavedInStagePerUser(usrStage.StageId);
                }

                var userStagesAsDRVentries =
                    from usrDrvEntry in db.Stages
                    where usrDrvEntry.DriverId == userID
                    select usrDrvEntry;

                foreach (var userStageAsDRV in userStagesAsDRVentries)
                {
                    userEmissionsSaved += AuxSavingsMethods.getEmissionsSavedInStagePerUser(userStageAsDRV.Id);
                }

            }
            catch (InvalidOperationException) { userEmissionsSaved = 0; }
            catch (NullReferenceException) { userEmissionsSaved = 0; }
            catch (System.Data.SqlClient.SqlException) { userEmissionsSaved = 0; }

            return userEmissionsSaved;

        }

        /// <summary>
        /// Calculates the fuel saved by a single user, both as driver or as passenger using TripLoa®
        /// </summary>
        /// <param name="userID">The Id of the user</param>
        /// <returns>the amount of fuel saved in liters</returns>
        public static double? getUserFuelSaved(int userID)
        {
            double? userFuelSaved = 0;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                var userStagesEntries =
                    from userStageEntry in db.Users_Stages
                    where userStageEntry.UserId == userID
                    select userStageEntry;

                foreach (var usrStage in userStagesEntries)
                {
                    userFuelSaved += AuxSavingsMethods.getFuelSavedInStagePerUser(usrStage.StageId);
                }

                var userStagesAsDRVentries =
                    from usrDrvEntry in db.Stages
                    where usrDrvEntry.DriverId == userID
                    select usrDrvEntry;

                foreach (var userStageAsDRV in userStagesAsDRVentries)
                {
                    userFuelSaved += AuxSavingsMethods.getFuelSavedInStagePerUser(userStageAsDRV.Id);
                }

            }
            catch (InvalidOperationException) { userFuelSaved = 0; }
            catch (NullReferenceException) { userFuelSaved = 0; }
            catch (System.Data.SqlClient.SqlException) { userFuelSaved = 0; }

            return userFuelSaved;

        }

        /// <summary>
        /// creates an object which contains all the data about a single user own savings
        /// </summary>
        /// <param name="userID">the user Id</param>
        /// <returns>A UserSavings object which contains the user savings stats</returns>
        public static UserSavings getUserSavings(int userID)
        {
            double? userEmissionsSaved = 0;
            double? userFuelSaved = 0;
            double userMoneySaved = 0;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                var userStagesEntries =
                    from userStageEntry in db.Users_Stages
                    where userStageEntry.UserId == userID
                    select userStageEntry;

                foreach (var usrStage in userStagesEntries)
                {
                    userEmissionsSaved += AuxSavingsMethods.getEmissionsSavedInStagePerUser(usrStage.StageId);
                    userFuelSaved += AuxSavingsMethods.getFuelSavedInStagePerUser(usrStage.StageId);
                }

                var userStagesAsDRVentries =
                    from usrDrvEntry in db.Stages
                    where usrDrvEntry.DriverId == userID
                    select usrDrvEntry;

                foreach (var userStageAsDRV in userStagesAsDRVentries)
                {
                    userEmissionsSaved += AuxSavingsMethods.getEmissionsSavedInStagePerUser(userStageAsDRV.Id);
                    userFuelSaved += AuxSavingsMethods.getFuelSavedInStagePerUser(userStageAsDRV.Id);
                }

                userMoneySaved = AuxSavingsMethods.getMoneySaved((double)userFuelSaved);

            }
            catch (NullReferenceException) { userEmissionsSaved = 0; userFuelSaved = 0; userMoneySaved = 0; }
            catch (InvalidOperationException) { userEmissionsSaved = 0; userFuelSaved = 0; userMoneySaved = 0; }
            catch (System.Data.SqlClient.SqlException) { userEmissionsSaved = 0; userFuelSaved = 0; userMoneySaved = 0; }

            return new UserSavings(userID, AuxMethods.TruncateTo3Decimals((double)userFuelSaved), AuxMethods.TruncateTo3Decimals((double)userEmissionsSaved), AuxMethods.TruncateTo3Decimals(userMoneySaved));

        }

        /// <summary>
        /// returns the number of missed appointment by the driver
        /// </summary>
        /// <param name="userID">the user id</param>
        /// <returns>the number of missed appointments by the user when partecipates in trips as driver (owner)</returns>
        public static int getUserAsDriverMissedAppointmentsCount(int userID)
        {
            int userAsDriverMissedAppointments = 0;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                var userAsDriverPacksEntries =
                     from feedbackEntry in db.DriverFeedbacks
                     where feedbackEntry.ReceiverUserId == userID && feedbackEntry.WasPresent == false
                     select feedbackEntry;

                List<int> TripsIDsWithMissedAppointments = new List<int>();

                foreach (var packEntry in userAsDriverPacksEntries)
                {
                    if (!TripsIDsWithMissedAppointments.Contains(packEntry.TripId)) TripsIDsWithMissedAppointments.Add(packEntry.TripId);
                }

                userAsDriverMissedAppointments = TripsIDsWithMissedAppointments.Count;

            }
            catch (InvalidOperationException) { userAsDriverMissedAppointments = 0; }
            catch (NullReferenceException) { userAsDriverMissedAppointments = 0; }
            catch (System.Data.SqlClient.SqlException) { userAsDriverMissedAppointments = 0; }

            return userAsDriverMissedAppointments;
        }

        /// <summary>
        /// returns the number of missed appointment by a passenger
        /// </summary>
        /// <param name="userID">the id of the passenger</param>
        /// <returns>the number of missed appointments by the user when partecipates in trips as passenger</returns>
        public static int getUserAsPassengerMissedAppointmentsCount(int userID)
        {
            int userAsPassengerMissedAppointments = 0;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                userAsPassengerMissedAppointments =
                     (from feedbackEntry in db.PassengerFeedbacks
                      where feedbackEntry.ReceiverUserId == userID && feedbackEntry.WasPresent == false
                      select feedbackEntry).Count();
            }
            catch (InvalidOperationException) { userAsPassengerMissedAppointments = 0; }
            catch (NullReferenceException) { userAsPassengerMissedAppointments = 0; }
            catch (System.Data.SqlClient.SqlException) { userAsPassengerMissedAppointments = 0; }

            return userAsPassengerMissedAppointments;
        }

        #endregion

        #region user-pending feedbacks methods

        /// <summary>
        /// insert a PendingFeedback in the pending feedback queue when the user join the trip: the boolean parameter
        /// "vehicleIsACar" is needed due to the feedback system semantic, which is different if the trip is by car or by
        /// another vehicle like a bike or a boat: in the first case the driver leaves a feedback per passenger and
        /// every passenger leaves a feedback to the driver, in the second case there isn't differences from driver and passengers
        /// so each pending feedback will be left from passenger to passenger
        /// </summary>
        /// <param name="tripID">the joined trip</param>
        /// <param name="psnID">the passenger ID</param>
        /// <param name="vehicleIsACar">set true if the vehicle is a car, false otherwise</param>
        /// <returns>false if the insertion fails, true otherwise</returns>
        public static bool insertPendingFeedbacksOnJoinOperation(int tripID, int psnID, bool vehicleIsACar)
        {
            bool operationOK = true;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                DateTime? tripDate = AuxTripMethods.getTripDepartureDate(tripID);

                if (tripDate != null)
                {
                    int? tripOwnerID = AuxTripMethods.getTripOwnerID(tripID);

                    if (tripOwnerID == null) { operationOK = false; }

                    else
                    {
                        using (TransactionScope t1 = new TransactionScope())
                        {

                            DateTime feedbackValidDate = ((DateTime)tripDate).AddDays(StatsModule.displayPendingFeedbackAfterDays);

                            PendingFeedbacks feedbackFromDRVtoPSN = new PendingFeedbacks();

                            feedbackFromDRVtoPSN.Date = feedbackValidDate;
                            feedbackFromDRVtoPSN.FeedbackType = 0;  // FEEDBACK FROM DRV TO PSN...TYPE = 0

                            feedbackFromDRVtoPSN.SenderUserId = (int)tripOwnerID;
                            feedbackFromDRVtoPSN.ReceiverUserId = psnID;
                            feedbackFromDRVtoPSN.TripId = tripID;

                            PendingFeedbacks feedbackFromPSNtoDRV = new PendingFeedbacks();

                            feedbackFromPSNtoDRV.Date = feedbackValidDate;

                            if (vehicleIsACar) feedbackFromPSNtoDRV.FeedbackType = 1;  // FEEDBACK FROM PSN TO DRV...TYPE = 1
                            else feedbackFromPSNtoDRV.FeedbackType = 0;  // FEEDBACK FROM DRV TO PSN...TYPE = 0

                            feedbackFromPSNtoDRV.SenderUserId = psnID;
                            feedbackFromPSNtoDRV.ReceiverUserId = (int)tripOwnerID;
                            feedbackFromPSNtoDRV.TripId = tripID;

                            db.PendingFeedbacks.InsertOnSubmit(feedbackFromDRVtoPSN);
                            db.PendingFeedbacks.InsertOnSubmit(feedbackFromPSNtoDRV);
                            db.SubmitChanges();

                            t1.Complete();
                        }
                    }

                }

                else { operationOK = false; }

            }
            catch (InvalidOperationException) { operationOK = false; }
            catch (NullReferenceException) { operationOK = false; }
            catch (System.Data.SqlClient.SqlException) { operationOK = false; }

            return operationOK;

        }

        /// <summary>
        /// the number of pending feedback the user has to leave yet
        /// </summary>
        /// <param name="loggedUserID">the user id</param>
        /// <returns>the number of pending feedbacks</returns>
        public static int hasPendingFeedbacksOperation(int loggedUserID)
        {
            int userPendingFeedbacksCount = 0;

            StatsModule.cleanExpiredPendingFeedbacks();

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                userPendingFeedbacksCount =
                    (from pendingFeedbacksEntry in db.PendingFeedbacks
                     where pendingFeedbacksEntry.SenderUserId == loggedUserID && (DateTime)pendingFeedbacksEntry.Date < DateTime.Today
                     select pendingFeedbacksEntry).Count();
            }
            catch (InvalidOperationException) { userPendingFeedbacksCount = 0; }
            catch (NullReferenceException) { userPendingFeedbacksCount = 0; }
            catch (System.Data.SqlClient.SqlException) { userPendingFeedbacksCount = 0; }

            return userPendingFeedbacksCount;

        }

        /// <summary>
        /// returns a list of trips joined or made by the user with pending feedbacks
        /// </summary>
        /// <param name="userID">the user ID</param>
        /// <returns>a list of TripProperties</returns>
        public static List<TripProperties> getUserTripsWithPendingFeedbacksOperation(int userID)
        {
            List<TripProperties> userTripsWithPendingFeedbacksList = new List<TripProperties>();

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                var userTripsIDWithPendingFeedbacks =
                    (from pendingFeedback in db.PendingFeedbacks
                     where pendingFeedback.SenderUserId == userID && (DateTime)pendingFeedback.Date < DateTime.Today
                     select pendingFeedback.TripId).Distinct();

                foreach (var tripEntry in userTripsIDWithPendingFeedbacks)
                {
                    TripProperties trInfo = AuxTripMethods.getTripProperties(tripEntry);

                    var trip =
                        (from tripEntries in db.Trips
                         where tripEntries.Id == tripEntry
                         select tripEntries).First();

                    if (trip.IsFrequent == true)
                    {
                        var pendingFdb =
                            (from pendingFeedback in db.PendingFeedbacks
                             where pendingFeedback.SenderUserId == userID && (DateTime)pendingFeedback.Date < DateTime.Today
                             select pendingFeedback).First();

                        trInfo.DepartureDate = pendingFdb.Date.AddDays(-StatsModule.displayPendingFeedbackAfterDays);

                    }

                    if (trInfo != null)
                    {
                        userTripsWithPendingFeedbacksList.Add(trInfo);
                    }

                    if (userTripsWithPendingFeedbacksList.Count() == StatsModule.MaxNumberOfUserTripsWithPendingFeedbacksToReturn) break;
                }

            }
            catch (InvalidOperationException) { userTripsWithPendingFeedbacksList = new List<TripProperties>(); }
            catch (NullReferenceException) { userTripsWithPendingFeedbacksList = new List<TripProperties>(); }
            catch (System.Data.SqlClient.SqlException) { userTripsWithPendingFeedbacksList = new List<TripProperties>(); }

            return userTripsWithPendingFeedbacksList;

        }

        /// <summary>
        /// calculates the feedbacks that has to be left for a trip
        /// </summary>
        /// <param name="loggedUserID">the user id</param>
        /// <param name="tripID">the trip id</param>
        /// <returns>the list of pending feedbacks</returns>
        public static List<FeedbackRequest> getPendingFeedbacksToLeaveForTripOperation(int loggedUserID, int tripID)
        {
            List<FeedbackRequest> feedbackRequestsList = new List<FeedbackRequest>();

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                var userPendingFeedbacksEntries =
                    from pendingFeedbacksEntry in db.PendingFeedbacks
                    where pendingFeedbacksEntry.SenderUserId == loggedUserID && (DateTime)pendingFeedbacksEntry.Date < DateTime.Today && pendingFeedbacksEntry.TripId == tripID
                    select pendingFeedbacksEntry;

                foreach (var userPendingFeedback in userPendingFeedbacksEntries)
                {
                    TripProperties trInfo = AuxTripMethods.getTripProperties(userPendingFeedback.TripId);

                    if (trInfo != null)
                    {
                        FeedbackRequest feedbackReq = new FeedbackRequest(loggedUserID, userPendingFeedback.ReceiverUserId, userPendingFeedback.FeedbackType, trInfo.DepartureDate, trInfo.OriginCity, trInfo.DestinationCity, userPendingFeedback.TripId);

                        //System.Console.WriteLine("FEEDBACK TO LEFT: ");
                        //System.Console.WriteLine("FROM: " + feedbackReq.SenderUsername + " TO: " + feedbackReq.ReceiverUsername + " TYPE: " + feedbackReq.FeedbackType);
                        //System.Console.WriteLine("FOR THE TRIP FROM: " + feedbackReq.TripStartCity + " TO: " + feedbackReq.TripDestinationCity + " OF " + feedbackReq.TripDepartureDate);
                        //System.Console.WriteLine();

                        feedbackRequestsList.Add(feedbackReq);
                    }

                }

            }
            catch (InvalidOperationException) { feedbackRequestsList = new List<FeedbackRequest>(); }
            catch (NullReferenceException) { feedbackRequestsList = new List<FeedbackRequest>(); }
            catch (System.Data.SqlClient.SqlException) { feedbackRequestsList = new List<FeedbackRequest>(); }

            return feedbackRequestsList;

        }

        /// <summary>
        /// delete the pending feedbacks when the user cancel the trip reservation
        /// </summary>
        /// <param name="tripID">the id of the trip</param>
        /// <param name="userID">the user id</param>
        /// <returns>false if the operation fail, true otherwise</returns>
        //public static bool deletePendingFeedbacksOnPassengerCancellationFromTripOperation(int tripID, int userID)
        //{
        //    bool removePendingFeedbacksOK = true;

        //    try
        //    {
        //        TripLoaDataContext db = StorageManager.Instance.Entities;

        //        var userPendingFeedbacks =
        //            from pendingFeedback in db.PendingFeedbacks
        //            where pendingFeedback.TripId == tripID && (pendingFeedback.SenderUserId == userID || pendingFeedback.ReceiverUserId == userID)
        //            select pendingFeedback;

        //        db.PendingFeedbacks.DeleteAllOnSubmit(userPendingFeedbacks);
                
        //        db.SubmitChanges();

        //    }
        //    catch (InvalidOperationException) { removePendingFeedbacksOK = false; }
        //    catch (NullReferenceException) { removePendingFeedbacksOK = false; }
        //    catch (System.Data.SqlClient.SqlException) { removePendingFeedbacksOK = false; }
        //    catch (TransactionAbortedException) {removePendingFeedbacksOK = false;}

        //    return removePendingFeedbacksOK;
        //}

        public static bool deletePendingFeedbacksFromThisUserForTripOperation(int userID, int tripID)
        {
            bool operationOK = true;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                var userPendingFeedbacksEntriesToRemove =
                    from pendingFeedbacksEntry in db.PendingFeedbacks
                    where pendingFeedbacksEntry.SenderUserId == userID && pendingFeedbacksEntry.TripId == tripID
                    select pendingFeedbacksEntry;

                db.PendingFeedbacks.DeleteAllOnSubmit(userPendingFeedbacksEntriesToRemove);
                db.SubmitChanges();

            }
            catch (InvalidOperationException) { operationOK = false; }
            catch (NullReferenceException) { operationOK = false; }
            catch (System.Data.SqlClient.SqlException) { operationOK = false; }

            return operationOK;
        }

        #endregion

        #region user - vehicle types

        /// <summary>
        /// set the type of vehicle owned by the user used to calculate user statistics about emissions and fuel saved
        /// If user selects NoOwnCar the system automatically assigns to him an utilitary car.
        /// </summary>
        /// <param name="userID">The user ID</param>
        /// <param name="vehicleType">The vehicle type defined in StatsModule.vehicleTypes enumeration</param>
        /// <returns>true if the operation is acomplished, false if an exception is raised</returns>
        public static bool setVehicleTypeOperation(int userID, int vehicleType)
        {
            bool operationOK = true;

            try
            {
                using (TransactionScope t = new TransactionScope())
                {

                    TripLoaDataContext db = StorageManager.Instance.Entities;

                    var userPreferences =
                        (from userEntries in db.UserPreferences
                         where userEntries.UserId == userID
                         select userEntries).First();

                    userPreferences.VechicleType = (byte)vehicleType;

                    var userEntry =
                        (from userEntries in db.Users
                         where userEntries.Id == userID
                         select userEntries).First();

                    switch (vehicleType)
                    {
                        case (int) StatsModule.vehicleTypes.UtilitaryCarVehicleType: // UTILITARY CAR TYPE SELECTED
                        {
                            userEntry.CarCo2Emissions = (int) StatsModule.UtilitaryCarsEmissionsReference;
                            userEntry.CarFuelConsumption = (float) StatsModule.UtilitaryCarsFuelConsumptionReference;
                            break;
                        }

                        case (int) StatsModule.vehicleTypes.SedanCarVehicleType:  // SEDAN CAR VEHICLE SELECTED
                        {
                            userEntry.CarCo2Emissions = (int) StatsModule.SedanCarsEmissionsReference;
                            userEntry.CarFuelConsumption = (float) StatsModule.SedanCarsFuelConsumptionReference;
                            break;
                        }

                        case (int)StatsModule.vehicleTypes.SportCarVehicleType:      // SPORT CAR VEHICLE SELECTED
                        {
                            userEntry.CarCo2Emissions = (int) StatsModule.SportCarsEmissionsReference;
                            userEntry.CarFuelConsumption = (float) StatsModule.SportCarsFuelConsumptionReference;
                            break;
                        }

                        case (int) StatsModule.vehicleTypes.SUVCarVehicleType:    // SUV CAR VECHICLE SELECTED
                        {
                            userEntry.CarCo2Emissions = (int) StatsModule.SUVCarsEmissionsReference;
                            userEntry.CarFuelConsumption = (float) StatsModule.SUVCarsFuelConsumptionReference;
                            break;
                        }

                        case (int) StatsModule.vehicleTypes.MPVCarVehicleType:    // Multi Purpose Vehicle SELECTED
                        {
                            userEntry.CarCo2Emissions = (int) StatsModule.MPVCarsEmissionsReference;
                            userEntry.CarFuelConsumption = (float) StatsModule.MPVCarsFuelConsumptionReference;
                            break;
                        }

                        
                        case (int) StatsModule.vehicleTypes.MotorbikeVehicleType:     // MOTORBIKE VEHICLE SELECTED
                        {
                            userEntry.CarCo2Emissions = (int) StatsModule.MotorbikeEmissionsReference;
                            userEntry.CarFuelConsumption = (float) StatsModule.MotorbikeFuelConsumptionReference;
                            break;
                        }


                        case (int) StatsModule.vehicleTypes.NoOwnVehicle:
                        {
                            userEntry.CarCo2Emissions = (int) StatsModule.UtilitaryCarsEmissionsReference;
                            userEntry.CarFuelConsumption = (float) StatsModule.UtilitaryCarsFuelConsumptionReference;
                            break;
                        }


                        default:
                        {
                            operationOK = false;
                            userEntry.CarCo2Emissions = (int) StatsModule.UtilitaryCarsEmissionsReference;
                            userEntry.CarFuelConsumption = (float) StatsModule.UtilitaryCarsFuelConsumptionReference;
                            break;
                        }

                    }

                    db.SubmitChanges();

                    t.Complete();
                }

            }
            catch (InvalidOperationException) { operationOK = false; }
            catch (NullReferenceException) { operationOK = false; }
            catch (InvalidCastException) { operationOK = false; }
            catch (System.Data.SqlClient.SqlException) { operationOK = false; }
            catch (TransactionAbortedException) { operationOK = false; }

            return operationOK;
        }

        /// <summary>
        /// Returns the vehicle type previously set by the user..by default a user has an utilitary car set.
        /// </summary>
        /// <param name="userID">The user ID</param>
        /// <returns>The vehicle type..vehicle types are defined in StatsModule.vehicleTypes enumeration</returns>
        public static int getVehicleTypeOperation(int userID)
        {
            int userVehicleType = -1;

            try
            {
                TripLoaDataContext db = StorageManager.Instance.Entities;

                userVehicleType =
                    (from userEntries in db.UserPreferences
                     where userEntries.UserId == userID
                     select userEntries.VechicleType).First();
            }
            catch (InvalidOperationException) { userVehicleType = -1; }
            catch (NullReferenceException) { userVehicleType = -1; }
            catch (InvalidCastException) { userVehicleType = -1; }
            catch (System.Data.SqlClient.SqlException) { userVehicleType = -1; }

            return userVehicleType;

        }

        #endregion

    }

}
